ListView bulk-edit using ADO.Net’s BatchUpdate and JQuery.

The following example enables bulk updating of records which are displayed in ListView. This example has one great feature. It updates the DB only for those records which are modified by the user. On the client end, a hidden value, saves all the rows which has been modified. Similarly on server, a batch update is done only for the affected rows. It’s efficient in the sense , the update happens only for affected rows and also the network roundtrip to the DB is optimum (based on the UpdateBatchSize value). JQuery helps in saving which all rows have been modified. Here’s the complete working example with relevant comments.

//aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ListViewBulkEdit2.aspx.cs" Inherits="ListViewBulkEdit2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.5.js" type="text/javascript"></script>
<script type="text/javascript">
    $(document).ready(function () {
        $("#lst td").change(function () {
            var t1 = $(this).siblings().find('input[type=hidden]')[0].value;
            var t2 = $("#hfRowsChanged")[0].value;
            if (t2 == "") {
                $("#hfRowsChanged")[0].value = t1;
            }
            else {
                $("#hfRowsChanged")[0].value += "," + t1;
            }
        });
    });

    
</script>
</head>
<body>
<form id="form1" runat="server">
<ajaxToolkit:ToolkitScriptManager runat="Server" ID="ScriptManager1"  />

<asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional"><ContentTemplate>
<div id="lst">
<asp:ListView ID="ListView1" runat="server" 
    onitemcommand="ListView1_ItemCommand" onitemdatabound="ListView1_ItemDataBound">
    <LayoutTemplate>
    <table>
    <tr>
        <td></td>
        <td>Col1</td>
        <td>Col2</td>
        <td>Col3</td>
        <td>Col4</td>
    </tr>
    <tr id="itemPlaceholder" runat="server"></tr>
    </table>
    </LayoutTemplate>
    <ItemTemplate>
    <tr>
        <asp:HiddenField ID="hf1" runat="server" Value='<%# DataBinder.Eval(Container, "DisplayIndex") %>' />
        <td><asp:Label ID="Label1" runat="server" Text='<%# Bind("PersonID") %>'></asp:Label></td>
        <td><asp:TextBox ID="txt1" runat="server" Text='<%# Bind("PFirstName") %>'></asp:TextBox></td>
        <td><asp:TextBox ID="txt2" runat="server" Text='<%# Bind("PLastName") %>'></asp:TextBox></td>
        <td><asp:TextBox ID="txt3" runat="server" Text='<%# Bind("PEmail") %>'></asp:TextBox></td>
        <td><asp:TextBox ID="txt4" runat="server" Text='<%# Bind("PZipCode") %>'></asp:TextBox></td>
        </tr>
    </ItemTemplate>
</asp:ListView>
    <br /><br />
<asp:Label ID="lblResult" runat="server" ></asp:Label>
<br /><br />
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Bulk Update" />
</div>
<asp:HiddenField ID="hfRowsChanged" runat="server" />
</ContentTemplate>
</asp:UpdatePanel>
</form>
</body>
</html>

//Codebehind

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public partial class ListViewBulkEdit2 : System.Web.UI.Page
{
private string _DBConnection1 = ConfigurationManager.AppSettings["conStr1"];
private static DataTable _dtClone = null;
    
protected void Page_Load(object sender, EventArgs e)
{
    lblResult.Text = "";
    if (!IsPostBack)
    {
        BindListView();
    }
}

private DataTable GetData2()
{
    SqlDataReader reader = null;
    DataTable dt = new DataTable();
    using (SqlConnection cn = new SqlConnection(this._DBConnection1))
    {
        cn.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "select PersonID,PFirstName,PLastName,PEmail,PZipCode from dbo.PersonDetails";
        cmd.CommandType = CommandType.Text;
        cmd.Connection = cn;
        reader = cmd.ExecuteReader();
        dt.Load(reader);
    }
    return dt;
}

protected void Button1_Click(object sender, EventArgs e)
{
    DataTable dt2 = _dtClone;
    dt2.TableName = "dbo.PersonDetails";
    dt2.AcceptChanges();
    if (hfRowsChanged.Value == "")
    {
        lblResult.Text = "No Records Updated.";
        return;
    }
    string[] _RowIdsChanged = hfRowsChanged.Value.Split(',');
    int prevVal = -1;
    foreach (string s1 in _RowIdsChanged)
    {
        if (int.Parse(s1) != prevVal) 
        //Sometimes the user may alter multiple times the same row. Hence this check.
        {
            DataRow dr1 = dt2.NewRow();
            dr1["PersonID"] = ((Label)(ListView1.Items[int.Parse(s1)].FindControl("Label1"))).Text;
            dr1["PFirstName"] = ((TextBox)(ListView1.Items[int.Parse(s1)].FindControl("txt1"))).Text;
            dr1["PLastName"] = ((TextBox)(ListView1.Items[int.Parse(s1)].FindControl("txt2"))).Text;
            dr1["PEmail"] = ((TextBox)(ListView1.Items[int.Parse(s1)].FindControl("txt3"))).Text;
            dr1["PZipCode"] = ((TextBox)(ListView1.Items[int.Parse(s1)].FindControl("txt4"))).Text;

            dt2.Rows.Add(dr1);
            prevVal = int.Parse(s1);
        }
    }
    dt2.AcceptChanges();
    //The below for-loop is dummy , just to make the rowstate as modified , so that batchUpdate understands the DB needs to be modified
    for (int u1 = 0; u1 < dt2.Rows.Count; u1++)
    {
        dt2.Rows[u1]["PersonID"] = dt2.Rows[u1]["PersonID"];
        dt2.Rows[u1]["PFirstName"] = dt2.Rows[u1]["PFirstName"];
        dt2.Rows[u1]["PLastName"] = dt2.Rows[u1]["PLastName"];
        dt2.Rows[u1]["PEmail"] = dt2.Rows[u1]["PEmail"];
        dt2.Rows[u1]["PZipCode"] = dt2.Rows[u1]["PZipCode"];
    }

    BatchUpdateDataTable(dt2);
    hfRowsChanged.Value = ""; //Initialize again
}

private void BatchUpdateDataTable(DataTable dt)
{
    using (SqlConnection connection = new SqlConnection(_DBConnection1))
    {
        SqlCommand command = new SqlCommand("update dbo.PersonDetails set PFirstName=@PFirstName, PLastName=@PLastName, PEmail=@PEmail, PZipCode=@PZipCode where PersonID = @PersonID", connection);
        command.CommandType = CommandType.Text;
        command.UpdatedRowSource = UpdateRowSource.None;

        command.Parameters.Add("@PersonID", SqlDbType.Int, 4, dt.Columns[0].ColumnName);
        command.Parameters.Add("@PFirstName", SqlDbType.VarChar, 100, dt.Columns[1].ColumnName);
        command.Parameters.Add("@PLastName", SqlDbType.VarChar, 100, dt.Columns[2].ColumnName);
        command.Parameters.Add("@PEmail", SqlDbType.VarChar, 100, dt.Columns[3].ColumnName);
        command.Parameters.Add("@PZipCode", SqlDbType.VarChar, 100, dt.Columns[4].ColumnName);
        command.UpdatedRowSource = UpdateRowSource.None;

        SqlDataAdapter adpt = new SqlDataAdapter();
        adpt.UpdateCommand = command;
        adpt.UpdateBatchSize = 10;
        
        connection.Open();
        int recordsUpdated = adpt.Update(dt);

        connection.Close();
        if (recordsUpdated > 0)
        {
            lblResult.Text = "Records Updated: " + recordsUpdated.ToString();
        }
    }

}

    
private void BindListView()
{
    DataTable dt = GetData2();
    _dtClone = dt.Clone();
    ListView1.DataSource = dt;
    ListView1.DataBind();
}

protected void ListView1_ItemCommand(object sender, ListViewCommandEventArgs e)
{

}

protected void ListView1_ItemDataBound(object sender, ListViewItemEventArgs e)
{
       
}

}

Thanks for reading.Hope this helps.

1 Response to ListView bulk-edit using ADO.Net’s BatchUpdate and JQuery.

Pest Scan says:

February 23, 2012 at 12:03 pm

The check for multiple updates to the same row fails if the rows are not editted in order (edit nr 1, then 3, then come back to 1). Suggested improvement:

IEnumerable RowIdsChangedList = _RowIdsChanged.Distinct();

foreach (string s1 in RowIdsChangedList)
{

DataRow dr1 = dt2.NewRow();
//Etc
dt2.Rows.Add(dr1);
}